SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[CreatePatientCode] 
(
	-- Add the parameters for the function here
	@Location NVARCHAR(5),@LastName NVARCHAR(1)
)
RETURNS NVARCHAR(11)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @OldPatientCode NVARCHAR(11)

	-- Add the T-SQL statements to compute the return value here
	SELECT TOP 1 @OldPatientCode=PatientCode FROM dbo.Patient WHERE PatientCode LIKE @Location + @LastName + '%'
	ORDER BY PatientCode DESC
	DECLARE @MaxCode INT
	IF (@@ROWCOUNT=0)
		SET @MaxCode = 1
	ELSE
		SET @MaxCode= CAST(RIGHT(@OldPatientCode,5) AS int )+1
	-- Return the result of the function
	RETURN @Location+ @LastName+ RIGHT('00000'+CONVERT(NVARCHAR(5),@MaxCode),5)
END
GO
